package com.fengxing.ams.web.facade.mouble.enrollment;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

public interface IMajorEnrollmentDAO  extends PagingAndSortingRepository<MajorEnrollmentVO, Integer>, JpaSpecificationExecutor<MajorEnrollmentVO>  {

	@Query(value = "select  t from MajorEnrollmentVO t where t.batch = :batch and t.subject = :subject and t.college_id = :college_id and t.major_id = :major_id  order by t.year ")
	List<MajorEnrollmentVO> queryEnrollmentGraph(@Param("batch")String batch, @Param("subject")String subject,
			@Param("college_id")Integer college_id, @Param("major_id")Integer major_id);
	
	@Query(value=
			        "SELECT                                           "+
					"  SUM(x.num) AS apply,                           "+
					"  y.id,                                          "+
					"  y.college_id,                                  "+
					"  y.major_id,                                    "+
					"  y.description_id,                              "+
					"  y.year,                                        "+
					"  y.batch,                                       "+
					"  y.subject,                                     "+
					"  y.max_score,                                   "+
					"  y.min_score,                                   "+
					"  y.avg_score,                                   "+
					"  y.min_ranking,                                 "+
					"  y.enrolled,                                    "+
					"  y.enroll_plan,                                 "+
					"  y.college_code,                                "+
					"  y.major_code                                   "+
					"FROM                                             "+
					"  zyt_major_enrollment Y,                        "+
					"  (SELECT                                        "+
					"    COUNT(1) AS num,                             "+
					"    b.weight,                                    "+
					"    a.year,                                      "+
					"    a.batch,                                     "+
					"    a.subject,                                   "+
					"    a.college_id,                                "+
					"    a.major_id                                   "+
					"  FROM                                           "+
					"    zyt_sys_weight b,                            "+
					"    zyt_student a                                "+
					"  WHERE (                                        "+
					"      a.apply_score BETWEEN :min_score           "+
					"      AND :max_score                             "+
					"    )                                            "+
					"    AND a.year = b.year                          "+
					"    AND IF(:batch != '', a.batch = :batch, '1=1')"+
					"    AND IF(                                      "+
					"      :SUBJECT != '',                            "+
					"      a.SUBJECT = :SUBJECT,                      "+
					"      '1=1'                                      "+
					"    )                                            "+
					"  GROUP BY a.year,                               "+
					"    a.batch,                                     "+
					"    a.subject,                                   "+
					"    a.college_id,                                "+
					"    a.major_id) AS x                             "+
					"WHERE x.year = y.year                            "+
					"  AND x.batch = y.batch                          "+
					"  AND x.subject = y.subject                      "+
					"  AND x.college_id = y.college_id                "+
					"  AND x.major_id = y.major_id                    "+
					"GROUP BY y.batch,                                "+
					"  y.subject,                                     "+
					"  y.college_id,                                  "+
					"  y.major_id                                     "+
					"HAVING SUM(x.weight) >= :weight_threshold        "+
					"ORDER BY SUM(x.num) DESC                              "+
					
					"limit :startIndex, :pageSize", nativeQuery=true)
	List<MajorEnrollmentVO> queryEnrollByScorePage(@Param("batch")String batch, @Param("SUBJECT")String subject,
			@Param("min_score")Integer min_score, @Param("max_score")Integer max_score, 
			@Param("weight_threshold")Integer weight_threshold, @Param("startIndex")Integer startIndex, @Param("pageSize")Integer pageSize);
	
	@Query(value=
	        "select count(1) from (SELECT                                           "+
			"  SUM(x.num) AS apply,                           "+
			"  y.id,                                          "+
			"  y.college_id,                                  "+
			"  y.major_id,                                    "+
			"  y.description_id,                              "+
			"  y.year,                                        "+
			"  y.batch,                                       "+
			"  y.subject,                                     "+
			"  y.max_score,                                   "+
			"  y.min_score,                                   "+
			"  y.avg_score,                                   "+
			"  y.min_ranking,                                 "+
			"  y.enrolled,                                    "+
			"  y.enroll_plan,                                 "+
			"  y.college_code,                                "+
			"  y.major_code                                   "+
			"FROM                                             "+
			"  zyt_major_enrollment y,                        "+
			"  (SELECT                                        "+
			"    COUNT(1) AS num,                             "+
			"    b.weight,                                    "+
			"    a.year,                                      "+
			"    a.batch,                                     "+
			"    a.subject,                                   "+
			"    a.college_id,                                "+
			"    a.major_id                                   "+
			"  FROM                                           "+
			"    zyt_sys_weight b,                            "+
			"    zyt_student a                                "+
			"  WHERE (                                        "+
			"      a.apply_score BETWEEN :min_score           "+
			"      AND :max_score                             "+
			"    )                                            "+
			"    AND a.year = b.year                          "+
			"    AND IF(:batch != '', a.batch = :batch, '1=1')"+
			"    AND IF(                                      "+
			"      :SUBJECT != '',                            "+
			"      a.SUBJECT = :SUBJECT,                      "+
			"      '1=1'                                      "+
			"    )                                            "+
			"  GROUP BY a.year,                               "+
			"    a.batch,                                     "+
			"    a.subject,                                   "+
			"    a.college_id,                                "+
			"    a.major_id) AS x                             "+
			"WHERE x.year = y.year                            "+
			"  AND x.batch = y.batch                          "+
			"  AND x.subject = y.subject                      "+
			"  AND x.college_id = y.college_id                "+
			"  AND x.major_id = y.major_id                    "+
			"GROUP BY y.batch,                                "+
			"  y.subject,                                     "+
			"  y.college_id,                                  "+
			"  y.major_id                                     "+
			"HAVING SUM(x.weight) >= :weight_threshold) z", nativeQuery=true)
Integer countEnrollByScorePage(@Param("batch")String batch, @Param("SUBJECT")String subject,
	@Param("min_score")Integer min_score, @Param("max_score")Integer max_score, 
	@Param("weight_threshold")Integer weight_threshold);
	
	@Query(value=
			"SELECT                                                 "+     
					"  b.*,                                                 "+
					"  ABS(CONVERT(avg_score - :score, SIGNED)) AS diss_score  "+
					"FROM                                                   "+
					"  zyt_major_enrollment b                               "+
					"WHERE YEAR = :year                                     "+
					"  AND SUBJECT = :subject                               "+
					"  AND IF(:batch != '', b.batch = :batch, '1=1')        "+
					"  AND IF(                                              "+
					"    :collegeFlag = 'true',                             "+
					"    b.college_id IN (:college_id),                     "+
					"    '1=1'                                              "+
					"  )                                                    "+
					"  AND IF(                                              "+
					"    :descriptionFlag = 'true',                         "+
					"    b.description_id IN (:description_id),             "+
					"    '1=1'                                              "+
					"  )                                                    "+
					"ORDER BY diss_score                                    ", nativeQuery=true)
	List<MajorEnrollmentVO> queryEnrollmentByYearOrderByDissScore(@Param("score")Integer score,@Param("batch")String batch,@Param("year")Integer year,@Param("subject")String subject, 
			@Param("collegeFlag")String collegeFlag, @Param("college_id")List<Integer> collegeIds,
			@Param("descriptionFlag")String descriptionFlag, @Param("description_id")List<Integer> descriptionIds);
	

	@Query(value="SELECT                                         "+ 
"  id                                            "+
"FROM                                           "+
"  zyt_major_enrollment b                        "+
"WHERE min_score < :max_score                   "+
"  AND max_score > :min_score                   "+
"  AND YEAR = :year                             "+
"  AND SUBJECT = :subject                       "+
"  AND IF(:batchFlag = 'true', b.batch IN (:batchs), '1=1')"+ 
"  AND IF(                                      "+
"    :collegeFlag = 'true',                     "+
"    b.college_id IN (:college_id),             "+
"    '1=1'                                      "+
"  )                                            "+
"  AND IF(                                      "+
"    :descriptionFlag = 'true',                 "+
"    b.description_id IN (:description_id),     "+
"    '1=1'                                      "+
"  )                                            "+
"  AND IF(:collegeTypeFlag = 'true', b.college_type IN (:collegeTypes), '1=1')"+ 
"  AND IF(:q_2 = 'on', b.q_2 =1, '1=1')"+ 
"  AND IF(:q_9 = 'on', b.q_9 =1, '1=1')"+ 
"  AND IF(:q_y = 'on', b.q_y =1, '1=1')",  nativeQuery=true)
	List<Integer> queryEnrollmentByScoreRangeAndSubjectAndYear(@Param("min_score")Integer min_score,@Param("max_score")Integer max_score,@Param("batchFlag")String batchFlag,@Param("batchs")List<String> batchs,@Param("year")Integer year,@Param("subject")String subject, 
			@Param("collegeFlag")String collegeFlag, @Param("college_id")List<Integer> collegeIds,
			@Param("descriptionFlag")String descriptionFlag, @Param("description_id")List<Integer> descriptionIds,
			@Param("collegeTypeFlag")String collegeTypeFlag, @Param("collegeTypes")List<String> collegeTypes, @Param("q_2")String q_2, @Param("q_9")String q_9, @Param("q_y")String q_y);
	
	@Query(value="SELECT                                         "+ 
			"  b.*                                            "+
			"FROM                                           "+
			"  zyt_major_enrollment b                        "+
			"WHERE min_score < :max_score                   "+
			"  AND max_score > :min_score                   "+
			"  AND YEAR = :year                             "+
			"  AND SUBJECT = :subject                       "+
			"  AND IF(:batchFlag = 'true', b.batch IN (:batchs), '1=1')"+ 
			"  AND IF(                                      "+
			"    :collegeFlag = 'true',                     "+
			"    b.college_id IN (:college_id),             "+
			"    '1=1'                                      "+
			"  )                                            "+
			"  AND IF(                                      "+
			"    :descriptionFlag = 'true',                 "+
			"    b.description_id IN (:description_id),     "+
			"    '1=1'                                      "+
			"  )                                            "+
			"  AND IF(:collegeTypeFlag = 'true', b.college_type IN (:collegeTypes), '1=1')"+ 
			"  AND IF(:q_2 = 'on', b.q_2 =1, '1=1')"+ 
			"  AND IF(:q_9 = 'on', b.q_9 =1, '1=1')"+ 
			"  AND IF(:q_y = 'on', b.q_y =1, '1=1')",  nativeQuery=true)
	List<MajorEnrollmentVO> queryEnrollmentVOByScoreRangeAndSubjectAndYear(@Param("min_score")Integer min_score,@Param("max_score")Integer max_score,@Param("batchFlag")String batchFlag,@Param("batchs")List<String> batchs,@Param("year")Integer year,@Param("subject")String subject, 
						@Param("collegeFlag")String collegeFlag, @Param("college_id")List<Integer> collegeIds,
						@Param("descriptionFlag")String descriptionFlag, @Param("description_id")List<Integer> descriptionIds,
						@Param("collegeTypeFlag")String collegeTypeFlag, @Param("collegeTypes")List<String> collegeTypes, @Param("q_2")String q_2, @Param("q_9")String q_9, @Param("q_y")String q_y);


}
